# author: zhaofeng-shu33
# 统计2018级新生缺失信息
import MySQLdb
import os
import argparse
import pdb
import csv
from program import web_api
from program import utility

if __name__ == '__main__':
    db = MySQLdb.connect(host ="localhost",user="root", db=os.getenv("db_name"), passwd=os.getenv("db_password"), charset="utf8")
    cursor = db.cursor()
    csv_select = ['student_name', 'student_dormitory', 'student_mobilephone', 'student_id']
    select_fields = ','.join(csv_select)
    cursor.execute('select {0} from qinghua where student_id like "112016%" and student_dormitory like "荷%"'.format(select_fields))
    for item in cursor.fetchall():
        item_list = list(item)
        student_name = item_list[0]
        student_dormitory = item_list[1]
        student_mobilephone = item_list[2]
        student_id = item_list[3]
        if student_dormitory is None:
            continue
        cursor.execute('select count(*) from qinghua where student_dormitory = "%s"' % student_dormitory)
        num = cursor.fetchone()
        if num[0] > 3:
            cursor.execute('update qinghua set student_dormitory = NULL where student_id = "%s"' % student_id)
        else:
            print(student_name, student_dormitory, student_mobilephone)
    db.commit()